using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using au.Swindles.CustomerEnquiryService.ObjectLayer;
using System.Data.SqlClient;
using System.Text;
using au.Swindles;


namespace au.Swindles.CustomerEnquiryService.AdapterLayer
{
    public static class CustomerAdapter
    {
        private static string getConnectionString()
        {
            return ConfigurationManager.AppSettings["DBConnection"];
        }

        private static SqlParameter getSqlInputParameter(string pParameterName, object pParameterValue, SqlDbType pParameterType )
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = pParameterName;
            param.Direction = ParameterDirection.Input;
            param.SqlDbType = pParameterType;
            param.Value = pParameterValue;

            return param;
        }
        public static List<Customer> getCustomerList()
        {
            List<Customer> customerList = new List<Customer>();
            SqlConnection connection = new SqlConnection(getConnectionString());
            try
            {
                string commandtext = "SELECT * FROM [dbo].[tblCustomer]";
                SqlCommand command = new SqlCommand(commandtext, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    customerList.Add(new Customer(  Utilities.getInt32(reader["customerID"])
                                                    ,Utilities.getString(reader["customerName"])
                                                    ,Utilities.getString(reader["customerNumber"])
                                                    ,Utilities.getInt32(reader["creditRating"])
                                                    ,Utilities.getString(reader["reason"])
                                                    ,Utilities.getString(reader["password"])
                                                    ,Utilities.getString(reader["email"])
                                                   ));
                }

                connection.Close();
                return customerList;
            }
            catch (Exception ex)
            {
                connection.Close();
                return null;
            }
        }

        public static void updateCustomer(Customer pCustomer)
        {
            SqlConnection connection = new SqlConnection(getConnectionString());
            StringBuilder commandtext = new StringBuilder();
            try
            {
                commandtext.Append(" UPDATE [dbo].[tblCustomer]");
                commandtext.Append(" SET (customerName = @pCustomerName, customerNumber = @pCustomerNumber, creditRating = @pCreditRating, reason = @pReason, password = @pPassword, email = @pEmail)");
                commandtext.Append(" WHERE customerID = @pCustomerID");
                SqlCommand command = new SqlCommand(commandtext.ToString(), connection);

                command.Parameters.Add(getSqlInputParameter("@pCustomerID", pCustomer.CusomerID, SqlDbType.Int));
                command.Parameters.Add(getSqlInputParameter("@pCustomerName", pCustomer.CustomerName, SqlDbType.VarChar));
                command.Parameters.Add(getSqlInputParameter("@pCustomerNumber", pCustomer.CustomerNumber, SqlDbType.VarChar));
                command.Parameters.Add(getSqlInputParameter("@pCreditRating", pCustomer.CreditRating, SqlDbType.Int));
                command.Parameters.Add(getSqlInputParameter("@pReason", pCustomer.Reason, SqlDbType.VarChar));
                command.Parameters.Add(getSqlInputParameter("@pPassword", pCustomer.Password, SqlDbType.VarChar));
                command.Parameters.Add(getSqlInputParameter("@pEmail", pCustomer.Email, SqlDbType.VarChar));
                
                connection.Open();

                command.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception ex)
            {
                connection.Close();
                throw ex;
            }
        }
    }
}
